﻿CREATE PROCEDURE [dbo].[CalculateAvgReports]
(
@DateB DATETIME, --Дата начала периода расчета уходимости
@DateE DATETIME, --Дата конца переода расчета уходимости
@DateB1 DATETIME,--Дата начала периода графика
@DateE1 DATETIME,--Дата конца периода графика
@DeptId INT, --Подразделение
@ContragentId INT --поставщик
)
AS
DECLARE @flag BIT
--Подготавливаем таблицу для расчета продаваемых количества проданных препаратов и их сумм
IF NOT EXISTS(SELECT * FROM tempdb.sys.objects WHERE ([type]=N'U') AND ([name] LIKE (N'%#MyAvgTABLE%')))
	--DROP TABLE #MyAvgTABLE
CREATE TABLE #MyAvgTABLE (GoodsId1 INT PRIMARY KEY CLUSTERED, Qntty INT, Price DECIMAL(19,2))
ELSE
	DELETE #MyAvgTABLE
--Заполняем товарами, которые были вначале и в конце
----------------------------------------------------------------------------------------------------
DECLARE @tmp1 table(id1 int primary key clustered)
DECLARE @tmp2 table(id2 int primary key clustered)
IF(@ContragentId IS NULL)
BEGIN
SET @flag = 0
INSERT INTO @tmp1(id1)
SELECT     CD.Parties.GoodsId
FROM         CA.Documents INNER JOIN
                      CA.GoodsDocLines ON CA.Documents.Id = CA.GoodsDocLines.DocId INNER JOIN
                      CD.Parties ON CA.GoodsDocLines.PartyId = CD.Parties.Id
WHERE     (CA.Documents.DocDate < @DateB1) AND (CA.Documents.DepartmentId = @DeptId)
GROUP BY CD.Parties.GoodsId
HAVING      (SUM(CA.GoodsDocLines.Qntty) > 0)
ORDER BY CD.Parties.GoodsId
INSERT INTO @tmp2(id2)
SELECT     CD.Parties.GoodsId
FROM         CA.Documents INNER JOIN
                      CA.GoodsDocLines ON CA.Documents.Id = CA.GoodsDocLines.DocId INNER JOIN
                      CD.Parties ON CA.GoodsDocLines.PartyId = CD.Parties.Id
WHERE     (CA.Documents.DocDate < @DateE1) AND (CA.Documents.DepartmentId = @DeptId)
GROUP BY CD.Parties.GoodsId
HAVING      (SUM(CA.GoodsDocLines.Qntty) > 0)
ORDER BY CD.Parties.GoodsId
END
ELSE
----------------------------------------------------------------------------------------------
BEGIN
SET @flag = 1
INSERT INTO @tmp1(id1)
SELECT     CD.Parties.GoodsId
FROM         CA.Documents INNER JOIN
                      CA.GoodsDocLines ON CA.Documents.Id = CA.GoodsDocLines.DocId INNER JOIN
                      CD.Parties ON CA.GoodsDocLines.PartyId = CD.Parties.Id
INNER JOIN
                      CD.InputLines ON CD.Parties.InputLineId = CD.InputLines.Id
AND (CD.InputLines.ContragentId = @ContragentId)
WHERE     (CA.Documents.DocDate < @DateB1) AND (CA.Documents.DepartmentId = @DeptId)
GROUP BY CD.Parties.GoodsId
HAVING      (SUM(CA.GoodsDocLines.Qntty) > 0)
ORDER BY CD.Parties.GoodsId
INSERT INTO @tmp2(id2)
SELECT     CD.Parties.GoodsId
FROM         CA.Documents INNER JOIN
                      CA.GoodsDocLines ON CA.Documents.Id = CA.GoodsDocLines.DocId INNER JOIN
                      CD.Parties ON CA.GoodsDocLines.PartyId = CD.Parties.Id
INNER JOIN
                      CD.InputLines ON CD.Parties.InputLineId = CD.InputLines.Id
AND (CD.InputLines.ContragentId = @ContragentId)
WHERE     (CA.Documents.DocDate < @DateE1) AND (CA.Documents.DepartmentId = @DeptId)
GROUP BY CD.Parties.GoodsId
HAVING      (SUM(CA.GoodsDocLines.Qntty) > 0)
ORDER BY CD.Parties.GoodsId
END
----------------------------------------------------------------------------------------------------
--Заполняем всеми товарами, проданными за этот промежуток
INSERT INTO #MyAvgTABLE(GoodsId1)
SELECT id1 FROM @tmp1 AS t1 inner join @tmp2 ON id1=id2
/*SELECT DISTINCT CD.Parties.GoodsId
FROM         CA.Documents INNER JOIN
CA.GoodsDocLines ON CA.Documents.Id = CA.GoodsDocLines.DocId
INNER JOIN CD.Parties ON CD.Parties.Id = CA.GoodsDocLines.PartyId
WHERE     (CA.Documents.DepartmentId = @DeptId) AND (CA.Documents.DocTypeId = 3) AND 
(CA.Documents.DocDate >= @DateB1 ) AND 
(CA.Documents.DocDate <= @DateE1)*/

--Заполняем проданными количествами
UPDATE #MyAvgTABLE 
SET Qntty = 
(SELECT  SUM(-g.Qntty) 
FROM         CA.Documents  d INNER JOIN
CA.GoodsDocLines  g ON d.Id = g.DocId
INNER JOIN CD.Parties p ON p.Id = g.PartyId
WHERE     (d.DepartmentId = @DeptId) AND 
(d.DocTypeId = 3) AND 
(d.DocDate >= @DateB) AND 
(d.DocDate <= @DateE) AND 
(GoodsId1 = p.GoodsId))
--Находим все дни продаж
DECLARE @tab TABLE( DocDate DATETIME PRIMARY KEY CLUSTERED, koef1 DECIMAL(19,2), koef2 DECIMAL(19,2))
INSERT INTO @tab(DocDate)
SELECT     CONVERT(DATETIME, CONVERT(VARCHAR(10), CA.Documents.DocDate, 102), 102) AS DocDate
FROM         CA.Documents
WHERE     (CA.Documents.DepartmentId = @DeptId) AND (CA.Documents.DocTypeId = 3)
AND CA.Documents.DocDate >= @DateB AND CA.Documents.DocDate <= @DateE
GROUP BY CONVERT(DATETIME, CONVERT(VARCHAR(10), CA.Documents.DocDate, 102), 102)
ORDER BY DocDate
--Заполняем среднеарифмитическое
UPDATE @tab SET koef1=dbo.GetAvg1(DocDate, @DeptId, @ContragentId)

DECLARE @DOCDATE DATETIME
DECLARE @KOEF1 DECIMAL(19,2)
DECLARE @KOEF2 DECIMAL(19,2)
DECLARE @Price DECIMAL(19,2)
DECLARE @Qntty INT

--проходим курсором по дням продаж
DECLARE CUR CURSOR FOR SELECT DocDate, koef1, koef2 FROM @tab
FOR UPDATE
OPEN CUR
FETCH NEXT FROM CUR
INTO @DOCDATE, @KOEF1, @KOEF2
--SET @KOEF2 = 2.22
WHILE @@FETCH_STATUS = 0
BEGIN
--обнуляем значения
UPDATE #MyAvgTABLE SET Price = 0
IF(@flag = 0)
BEGIN
--находим среднюю цену товара, если в этот день небыло продаж - NULL
		UPDATE #MyAvgTABLE SET Price = 
		(SELECT     AVG(p.RetailPrice)
		FROM         CA.GoodsDocLines AS g INNER JOIN
							  CA.Documents AS d ON g.DocId = d.Id INNER JOIN
							  CD.Parties AS p ON g.PartyId = p.Id
		WHERE     (d.DocDate < DATEADD(dd,1,@DOCDATE)) AND (d.DepartmentId = @DeptId) and (p.GoodsId = GoodsId1)
		GROUP BY /*g.PartyId,*/  p.GoodsId
		HAVING      (SUM(g.Qntty) > 0))
END
ELSE
BEGIN

		UPDATE #MyAvgTABLE SET Price = 
		(SELECT     AVG(p.RetailPrice)
		FROM         CA.GoodsDocLines AS g INNER JOIN
							  CA.Documents AS d ON g.DocId = d.Id INNER JOIN
							  CD.Parties AS p ON g.PartyId = p.Id INNER JOIN
							  CD.InputLines as i ON i.Id = p.InputLineId AND
							  i.ContragentId = @ContragentId
		WHERE     (d.DocDate < DATEADD(dd,1,@DOCDATE)) AND (d.DepartmentId = @DeptId) and 
		(p.GoodsId = GoodsId1)
		GROUP BY /*g.PartyId,*/  p.GoodsId
		HAVING      (SUM(g.Qntty) > 0))

END
SELECT @Qntty = sum(Qntty) FROM #MyAvgTABLE WHERE Price >0
SELECT @Price = sum(Qntty * Price)/@Qntty FROM #MyAvgTABLE

UPDATE @tab SET koef2 = @Price WHERE DocDate = @DOCDATE

FETCH NEXT FROM CUR
INTO @DOCDATE, @KOEF1, @KOEF2
END

CLOSE CUR
DEALLOCATE CUR
SELECT DocDate, koef1, koef2 FROM @tab